SQL SQLite 通用系列 数据存储
摘要：
主要内容：
本文介绍了封装后的SQLite，以便快速开发；
只保留必要的定制，重复的代码封装到一个通用类中；
说明
LouSQLite.java文件为通用的代码，所有项目中不需要修改即可使用； MyCallBack.java文件是自定义的文件，关于项目的数据库配置都在这里进行，例如：数据库名称、数据库版本号、table语句等； LouSQLite.java支持常用的CRUD操作（支持事务）；
代码
【用法】
使用方法
lists = Arrays.asList(
new Phrase("窈窕淑女，君子好逑"),
new Phrase("海上生明月，天涯共此时"),
new Phrase("青青子衿，悠悠我心"),
new Phrase("人生若只如初见")
);
LouSQLite.insert(MyCallBack.TABLE_PHRASE, lists);
// 更新到数据库
LouSQLite.update(MyCallBack.TABLE_PHRASE, phrase, MyCallBack.KEY_PHRASE_ID + "=http://www.it165.net/database/html/201607/?", new String[]{phrase.getId()});
// 查找
List lists = LouSQLite.query(MyCallBack.TABLE_PHRASE, "select * from " + MyCallBack.TABLE_PHRASE, null);
// 从数据库中删除
LouSQLite.delete(MyCallBack.TABLE_PHRASE, MyCallBack.KEY_PHRASE_ID + "=http://www.it165.net/database/html/201607/?", new String[]{phrase.getId()});' data-snippet-id='ext.276d89f67b30182ceedfa7eb4506bd05' data-snippet-saved='false' data-codota-status='done'>// 初始化
LouSQLite.init(mContext, MyCallBack.getInstance());
Phrase phrase = new Phrase('青青子衿，悠悠我心');
// 插入一个数据到数据库
LouSQLite.insert(MyCallBack.TABLE_PHRASE, phrase);
// 插入一组数据
List<Phrase> lists = Arrays.asList(
new Phrase('窈窕淑女，君子好逑'),
new Phrase('海上生明月，天涯共此时'),
new Phrase('青青子衿，悠悠我心'),
new Phrase('人生若只如初见')
);
LouSQLite.insert(MyCallBack.TABLE_PHRASE, lists);
// 更新到数据库
LouSQLite.update(MyCallBack.TABLE_PHRASE, phrase, MyCallBack.KEY_PHRASE_ID + '=http://www.it165.net/database/html/201607/?', new String[]{phrase.getId()});
// 查找
List<Phrase> lists = LouSQLite.query(MyCallBack.TABLE_PHRASE, 'select * from ' + MyCallBack.TABLE_PHRASE, null);
// 从数据库中删除
LouSQLite.delete(MyCallBack.TABLE_PHRASE, MyCallBack.KEY_PHRASE_ID + '=http://www.it165.net/database/html/201607/?', new String[]{phrase.getId()});
【数据库定制】
createTablesSQL() {
return Arrays.asList(
TABLE_PHRASE_SQL,
TABLE_FAVORITE_SQL
);
}
@Override
public String getName() {
return DB_NAME;
}
@Override
public int getVersion() {
return DB_VERSION;
}
@Override
public void doUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
case 0:
db.execSQL(TABLE_FAVORITE_SQL); // 升级操作；
case 1:
break;
default:
break;
}
}
@Override
public void assignValuesByInstance(String tableName, T t, ContentValues values) {
switch (tableName) {
case TABLE_PHRASE:
if (t instanceof Phrase) {
Phrase phrase = (Phrase) t;
values.put(KEY_PHRASE_ID, phrase.getId());
values.put(KEY_PHRASE_CONTENT, phrase.getContent());
values.put(KEY_PHRASE_FAVORITE, phrase.getFavorite());
}
break;
case TABLE_FAVORITE:
if (t instanceof Phrase) {
Phrase phrase = (Phrase) t;
values.put(KEY_FAVORITE_ID, phrase.getId());
values.put(KEY_FAVORITE_CONTENT, phrase.getContent());
values.put(KEY_FAVORITE_FAVORITE, phrase.getFavorite());
}
break;
}
}
@Override
public Object newInstanceByCursor(String tableName, Cursor cursor) {
switch (tableName) {
case TABLE_PHRASE:
return new Phrase(
cursor.getString(cursor.getColumnIndex(KEY_PHRASE_ID)),
cursor.getString(cursor.getColumnIndex(KEY_PHRASE_CONTENT)),
cursor.getInt(cursor.getColumnIndex(KEY_PHRASE_FAVORITE))
);
case TABLE_FAVORITE:
return new Phrase(
cursor.getString(cursor.getColumnIndex(KEY_FAVORITE_ID)),
cursor.getString(cursor.getColumnIndex(KEY_FAVORITE_CONTENT)),
cursor.getInt(cursor.getColumnIndex(KEY_FAVORITE_FAVORITE))
);
}
return null;
}
}
' data-snippet-id='ext.2f8e0087efc4bc22327aa5b639d59242' data-snippet-saved='false' data-codota-status='done'>//: MyCallBack.java
package com.lyloou.lou.db;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.lyloou.lou.bean.Phrase;
import java.util.Arrays;
import java.util.List;
/**
* 类描述：
* 创建人： Lou
* 创建时间： 2016/7/13 14:22
* 修改人： Lou
* 修改时间：2016/7/13 14:22
* 修改备注：
*/
public class MyCallBack implements LouSQLite.ICallBack {
private static final String TAG = 'MyCallBack';
private MyCallBack() {
}
private static LouSQLite.ICallBack INSTANCE;
public static LouSQLite.ICallBack getInstance() {
if (INSTANCE == null) {
INSTANCE = new MyCallBack();
}
return INSTANCE;
}
///////////////////////////////////////////////////////////////////////////
// db config
///////////////////////////////////////////////////////////////////////////
public static final String DB_NAME = 'DBNAME.db';
public static final int DB_VERSION = 1;
///////////////////////////////////////////////////////////////////////////
// table phrase
///////////////////////////////////////////////////////////////////////////
public static final String TABLE_PHRASE = 'phrase';
public static final String KEY_PHRASE_ID = 'PHRASE_ID';
private static final String KEY_PHRASE_CONTENT = 'PHRASE_CONTENT';
private static final String KEY_PHRASE_FAVORITE = 'PHRASE_FAVORITE';
private static final String TABLE_PHRASE_SQL = 'create table ' + TABLE_PHRASE + ' (' +
'id integer primary key autoincrement, ' +
KEY_PHRASE_ID + ' text, ' +
KEY_PHRASE_CONTENT + ' text, ' +
KEY_PHRASE_FAVORITE + ' integer' +
')';
///////////////////////////////////////////////////////////////////////////
// table favorite
///////////////////////////////////////////////////////////////////////////
public static final String TABLE_FAVORITE = 'favorite';
public static final String KEY_FAVORITE_ID = 'FAVORITE_ID';
private static final String KEY_FAVORITE_CONTENT = 'FAVORITE_CONTENT';
private static final String KEY_FAVORITE_FAVORITE = 'FAVORITE_FAVORITE';
private static final String TABLE_FAVORITE_SQL = 'create table ' + TABLE_FAVORITE + ' (' +
'id integer primary key autoincrement, ' +
KEY_FAVORITE_ID + ' text, ' +
KEY_FAVORITE_CONTENT + ' text,' +
KEY_FAVORITE_FAVORITE + ' integer' +
')';
///////////////////////////////////////////////////////////////////////////
// overrite
///////////////////////////////////////////////////////////////////////////
@Override
public List<String> createTablesSQL() {
return Arrays.asList(
TABLE_PHRASE_SQL,
TABLE_FAVORITE_SQL
);
}
@Override
public String getName() {
return DB_NAME;
}
@Override
public int getVersion() {
return DB_VERSION;
}
@Override
public void doUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
case 0:
db.execSQL(TABLE_FAVORITE_SQL); // 升级操作；
case 1:
break;
default:
break;
}
}
@Override
public <T> void assignValuesByInstance(String tableName, T t, ContentValues values) {
switch (tableName) {
case TABLE_PHRASE:
if (t instanceof Phrase) {
Phrase phrase = (Phrase) t;
values.put(KEY_PHRASE_ID, phrase.getId());
values.put(KEY_PHRASE_CONTENT, phrase.getContent());
values.put(KEY_PHRASE_FAVORITE, phrase.getFavorite());
}
break;
case TABLE_FAVORITE:
if (t instanceof Phrase) {
Phrase phrase = (Phrase) t;
values.put(KEY_FAVORITE_ID, phrase.getId());
values.put(KEY_FAVORITE_CONTENT, phrase.getContent());
values.put(KEY_FAVORITE_FAVORITE, phrase.getFavorite());
}
break;
}
}
@Override
public Object newInstanceByCursor(String tableName, Cursor cursor) {
switch (tableName) {
case TABLE_PHRASE:
return new Phrase(
cursor.getString(cursor.getColumnIndex(KEY_PHRASE_ID)),
cursor.getString(cursor.getColumnIndex(KEY_PHRASE_CONTENT)),
cursor.getInt(cursor.getColumnIndex(KEY_PHRASE_FAVORITE))
);
case TABLE_FAVORITE:
return new Phrase(
cursor.getString(cursor.getColumnIndex(KEY_FAVORITE_ID)),
cursor.getString(cursor.getColumnIndex(KEY_FAVORITE_CONTENT)),
cursor.getInt(cursor.getColumnIndex(KEY_FAVORITE_FAVORITE))
);
}
return null;
}
}
//: Pharse.java
package com.lyloou.lou.bean;
import java.util.UUID;
/**
* 类描述：
* 创建人： Lou
* 创建时间： 2016/7/12 9:49
* 修改人： Lou
* 修改时间：2016/7/12 9:49
* 修改备注：
*/
public class Phrase {
private String mId;
private String mContent;
private int mFavorite; // 0表示未收藏，1表示已收藏；
public Phrase(String content) {
this(UUID.randomUUID().toString(), content, 0);
}
public Phrase(String content, int favorite) {
this(UUID.randomUUID().toString(), content, favorite);
}
public Phrase(String id, String content, int favorite) {
mId = id;
mContent = content;
mFavorite = favorite;
}
public String getId() {
return mId;
}
public void setId(String id) {
mId = id;
}
public String getContent() {
return mContent;
}
public void setContent(String content) {
mContent = content;
}
public int getFavorite() {
return mFavorite;
}
public void setFavorite(int favorite) {
mFavorite = favorite;
}
}
【通用源码】
createTablesSQL();
void assignValuesByInstance(String tableName, T t, ContentValues values);
T newInstanceByCursor(String tableName, Cursor cursor);
}
private static final String ILLEGAL_OPREATION = "非法操作，请先进行初始化操作：LouSQLite.init()";
private static LouSQLite INSTANCE;
public static void init(Context context, ICallBack callBack) {
if (INSTANCE == null) {
INSTANCE = new LouSQLite(context, callBack);
}
}
public static void insert(String tableName, T t) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
INSTANCE.mCallBack.assignValuesByInstance(tableName, t, values);
db.insert(tableName, null, values);
values.clear();
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static void insert(String tableName, List ts) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
for (T t : ts) {
INSTANCE.mCallBack.assignValuesByInstance(tableName, t, values);
db.insert(tableName, null, values);
values.clear();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static void update(String tableName, T t, String whereClause, String[] whereArgs) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
INSTANCE.mCallBack.assignValuesByInstance(tableName, t, values);
db.update(tableName, values, whereClause, whereArgs);
values.clear();
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static void update(String tableName, List ts, String whereClause, String[] whereArgs) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
for (T t : ts) {
INSTANCE.mCallBack.assignValuesByInstance(tableName, t, values);
db.update(tableName, values, whereClause, whereArgs);
values.clear();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static void delete(String tableName, String whereClause, String[] whereArgs) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
db.delete(tableName, whereClause, whereArgs);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static List query(String tableName, @NonNull String queryStr, @Nullable String[] whereArgs) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
List lists = new ArrayList<>();
SQLiteDatabase db = INSTANCE.getReadableDatabase();
db.beginTransaction();
try {
db.setTransactionSuccessful();
Cursor cursor = db.rawQuery(queryStr, whereArgs);
if (cursor.moveToFirst()) {
do {
T tt = INSTANCE.mCallBack.newInstanceByCursor(tableName, cursor);
if (tt != null) {
lists.add(tt);
}
} while (cursor.moveToNext());
}
cursor.close();
} finally {
db.endTransaction();
db.close();
}
return lists;
}
///////////////////////////////////////////////////////////////////////////
// Self
///////////////////////////////////////////////////////////////////////////
private final ICallBack mCallBack;
private static final String TAG = "LouSQLite";
private LouSQLite(@NonNull Context context, @NonNull ICallBack callBack) {
super(context, callBack.getName(), null, callBack.getVersion());
mCallBack = callBack;
}
@Override
public void onCreate(SQLiteDatabase db) {
for (String create_table : mCallBack.createTablesSQL()) {
db.execSQL(create_table);
Log.d(TAG, "create table " + "[
" + create_table + "
]" + " successful! ");
}
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
mCallBack.doUpgrade(sqLiteDatabase, oldVersion, newVersion);
}
}
' data-snippet-id='ext.11a8b43c3fe96cada084d84e93618e2f' data-snippet-saved='false' data-codota-status='done'>//: LouSQLite.java
package com.lyloou.lou.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;
/**
* 类描述：一个通用的SQLite，通过简单的配置快速搭建一个数据库存储方案；
* 创建人： Lou
* 创建时间： 2016/7/13 10:10
* 修改人： Lou
* 修改时间：2016/7/13 10:10
* 修改备注：
*/
public class LouSQLite extends SQLiteOpenHelper {
public interface ICallBack {
String getName();
int getVersion();
void doUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);
List<String> createTablesSQL();
<T> void assignValuesByInstance(String tableName, T t, ContentValues values);
<T> T newInstanceByCursor(String tableName, Cursor cursor);
}
private static final String ILLEGAL_OPREATION = '非法操作，请先进行初始化操作：LouSQLite.init()';
private static LouSQLite INSTANCE;
public static void init(Context context, ICallBack callBack) {
if (INSTANCE == null) {
INSTANCE = new LouSQLite(context, callBack);
}
}
public static <T> void insert(String tableName, T t) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
INSTANCE.mCallBack.assignValuesByInstance(tableName, t, values);
db.insert(tableName, null, values);
values.clear();
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static <T> void insert(String tableName, List<T> ts) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
for (T t : ts) {
INSTANCE.mCallBack.assignValuesByInstance(tableName, t, values);
db.insert(tableName, null, values);
values.clear();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static <T> void update(String tableName, T t, String whereClause, String[] whereArgs) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
INSTANCE.mCallBack.assignValuesByInstance(tableName, t, values);
db.update(tableName, values, whereClause, whereArgs);
values.clear();
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static <T> void update(String tableName, List<T> ts, String whereClause, String[] whereArgs) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
for (T t : ts) {
INSTANCE.mCallBack.assignValuesByInstance(tableName, t, values);
db.update(tableName, values, whereClause, whereArgs);
values.clear();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static void delete(String tableName, String whereClause, String[] whereArgs) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
db.delete(tableName, whereClause, whereArgs);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static <T> List<T> query(String tableName, @NonNull String queryStr, @Nullable String[] whereArgs) {
if (INSTANCE == null) {
throw new IllegalStateException(ILLEGAL_OPREATION);
}
List<T> lists = new ArrayList<>();
SQLiteDatabase db = INSTANCE.getReadableDatabase();
db.beginTransaction();
try {
db.setTransactionSuccessful();
Cursor cursor = db.rawQuery(queryStr, whereArgs);
if (cursor.moveToFirst()) {
do {
T tt = INSTANCE.mCallBack.newInstanceByCursor(tableName, cursor);
if (tt != null) {
lists.add(tt);
}
} while (cursor.moveToNext());
}
cursor.close();
} finally {
db.endTransaction();
db.close();
}
return lists;
}
///////////////////////////////////////////////////////////////////////////
// Self
///////////////////////////////////////////////////////////////////////////
private final ICallBack mCallBack;
private static final String TAG = 'LouSQLite';
private LouSQLite(@NonNull Context context, @NonNull ICallBack callBack) {
super(context, callBack.getName(), null, callBack.getVersion());
mCallBack = callBack;
}
@Override
public void onCreate(SQLiteDatabase db) {
for (String create_table : mCallBack.createTablesSQL()) {
db.execSQL(create_table);
Log.d(TAG, 'create table ' + '[
' + create_table + '
]' + ' successful! ');
}
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
mCallBack.doUpgrade(sqLiteDatabase, oldVersion, newVersion);
}
}
mysql
sqlite只通过文件锁就可以
返回的是一个SQLiteDatabas
只不过它是OC方式封装了
应该增加autoincrementcreate
如果没有就从Bundle中把数
Linux 部署ASP.NET SQLite 应用
只有被 sqlite3_bind_value()和
iOS开拓之SQLiteC语言接口类
11SQLite之view(视图)
解压后拷贝出sqlite3文件到便于CMD命令行便
SQLite的架构(TheArchitectureOfSQLite)
Android数据存储的三种方法SharedPrefrences
Android数据存储三剑客SharedPreferences File
sQlite常用语句以及sQlite developer的使用与注
3.2基于MBTiles规范进行存储 设计思路为：
SQLite数据库管理系统-我所认识的数据库引